package cn.xetsoft.supermarket.dao.impl;

import cn.xetsoft.supermarket.dao.DbDao;
import cn.xetsoft.supermarket.util.JDBCUtils;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author 侯国强
 * @version 1.0
 * @Description 数据库功能基本操作
 * @since 2020/12/23 15:30
 */
@Slf4j //使用lombok日志的注解
public class DbDaoImpl implements DbDao {

    /**
     * 使用数据库
     */
    @Override
    public int useDatabase(String dbName) throws SQLException {
        Connection conn = JDBCUtils.getInstanceConnection();
        //sql
        String sql = "USE " + dbName +";";
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //打印debug日志
        log.debug("run sql : {}",sql);
        //执行并返回结果
        return ptmt.executeUpdate();
    }

    /**
     * 创建数据库
     */
    @Override
    public int createDatabase(String dbName) throws SQLException {
        Connection conn = JDBCUtils.getInstanceConnection();
        //sql
        String sql = "CREATE DATABASE "+dbName+" DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
        //打印debug日志
        log.debug("run sql : {}",sql);
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //执行并返回结果
        return ptmt.executeUpdate();
    }

    /**
     * 创建数据表
     * 需要提供数据表创建sql
     */
    @Override
    public int createTable(String sql) throws SQLException {
        Connection conn = JDBCUtils.getInstanceConnection();
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //打印debug日志
        log.debug("run sql : {}",sql);
        //执行并返回结果
        return ptmt.executeUpdate();
    }

    /**
     * 删除数据库
     */
    @Override
    public int deleteDatabase(String dbName) throws SQLException {
        Connection conn = JDBCUtils.getInstanceConnection();
        //sql
        String sql = "drop database "+dbName+";";
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //打印debug日志
        log.debug("run sql : {}",sql);
        //执行并返回结果
        return ptmt.executeUpdate();
    }

    /**
     * 判断数据库是否存在
     */
    @Override
    public boolean existDatabase(String dbName) throws SQLException {
        //SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME='skt100_200';
        if (null == dbName){
            return false;
        }
        Connection conn = JDBCUtils.getInstanceConnection();
        //sql
        String sql = "SELECT COUNT(1) AS count FROM information_schema.SCHEMATA where SCHEMA_NAME=?";
        //预编译
        PreparedStatement ptmt  = conn.prepareStatement(sql);
        //传参
        ptmt.setString(1,dbName);
        //执行并返回结果
        ResultSet resultSet = ptmt.executeQuery();
        //打印debug日志
        log.debug("run sql : {}",sql);
        log.debug("param -> {}",dbName);
        while (resultSet.next()){
            int count = resultSet.getInt("count");
            if (count > 0){
                return true;
            }
        }
        return false;
    }

    /**
     * 判断数据表是否存在
     */
    @Override
    public boolean existTable(String dbName,String tbName) throws SQLException {
        //SELECT table_name FROM information_schema.TABLES WHERE table_name ='card';
        if (tbName == null){
            return false;
        }
        Connection conn = JDBCUtils.getInstanceConnection();
        //sql
        String sql = "SELECT table_name FROM information_schema.TABLES WHERE table_name=? AND table_schema=?;";
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //传参
        ptmt.setString(1, tbName);
        ptmt.setString(2, dbName);
        //打印debug日志
        log.debug("run sql : {}",sql);
        log.debug("param -> {}",tbName);
        //执行并返回结果
        ResultSet resultSet = ptmt.executeQuery();
        while (resultSet.next()){
            String table_name = resultSet.getString("table_name");
            System.out.println(table_name);
            if (table_name != null){
                return true;
            }
        }
        return false;
    }
}
